package chinesechess.DTO;

import java.sql.*;

public class DBConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/chess_game?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASSWORD = "511511";
    
    public static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            throw new SQLException("MySQL JDBC Driver not found.");
        }
    }
    
    // 创建用户表
    public static void createUserTable() {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(50) UNIQUE NOT NULL," +
                    "password VARCHAR(50) NOT NULL," +
                    "nickname VARCHAR(50) NOT NULL)";
                    
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("Users table created or already exists.");
        } catch (SQLException e) {
            System.err.println("Error creating users table: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    // 创建积分表
    public static void createScoreTable() {
        String sql = "CREATE TABLE IF NOT EXISTS score (" +
                    "user_id INT PRIMARY KEY," +
                    "score INT DEFAULT 0," +
                    "user_nickname VARCHAR(50)," +
                    "FOREIGN KEY (user_id) REFERENCES users(id))";
                    
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("Score table created or already exists.");
            
            // 检查是否需要为现有用户初始化积分记录
            String initScores = "INSERT IGNORE INTO score (user_id, score, user_nickname) " +
                              "SELECT id, 0, nickname FROM users";
            stmt.execute(initScores);
            System.out.println("Initialized scores for existing users.");
        } catch (SQLException e) {
            System.err.println("Error creating score table: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    // 初始化用户积分记录
    public static void initUserScore(int userId, String nickname) {
        String sql = "INSERT INTO score (user_id, score, user_nickname) VALUES (?, 0, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setString(2, nickname);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 创建对战记录表
    public static void createBattleRecordTable() {
        String sql = "CREATE TABLE IF NOT EXISTS laiyuan (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "user_id INT," +
                    "score INT," +
                    "duizhan VARCHAR(20)," +
                    "user_nickname VARCHAR(50)," +
                    "battle_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                    "FOREIGN KEY (user_id) REFERENCES users(id))";
                    
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("Battle record table created or already exists.");
        } catch (SQLException e) {
            System.err.println("Error creating battle record table: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    // 添加对战记录
    public static void addBattleRecord(int userId, int score, String duizhan, String nickname) {
        String sql = "INSERT INTO laiyuan (user_id, score, duizhan, user_nickname) VALUES (?, ?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, score);
            pstmt.setString(3, duizhan);
            pstmt.setString(4, nickname);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
} 